package org.example.neusoft.dao;


import org.example.neusoft.model.Article;
import org.example.neusoft.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class ArticleDao {
    public List<Map<String, String>> getAllArticleLst() {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        try {
            String sql = "select articles.id,articles.title, articles.body, articles.created_at,\n" +
                    "users.username, ifnull(tmptab.taglist,'') as taglist from articles\n" +
                    "left join users on articles.user_id = users.id\n" +
                    "left join (select article_id, GROUP_CONCAT(tags.name) as taglist from article_tags\n" +
                    "left join tags on article_tags.tag_id = tags.id\n" +
                    "GROUP BY article_id) as tmptab on tmptab.article_id = articles.id";

            ps = connection.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            List<Map<String, String>> mapLst = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, String> map = new HashMap<>();
                map.put("id", resultSet.getString("id"));
                map.put("title", resultSet.getString("title"));
                map.put("body", resultSet.getString("body"));
                map.put("created_at", resultSet.getString("created_at"));
                map.put("username", resultSet.getString("username"));
                map.put("taglist", resultSet.getString("taglist"));
                mapLst.add(map);
            }
            return mapLst;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public void createArticle(Article article) {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        try {
            String sqlInsert = "insert into articles values(?,?,?,?,?,?)";
            ps = connection.prepareStatement(sqlInsert);
            ps.setString(1, article.getId());
            ps.setString(2, article.getUser_id());
            ps.setString(3, article.getTitle());
            ps.setString(4, article.getBody());
            ps.setString(5, article.getCreated_at());
            ps.setString(6, article.getUpdated_at());
            ps.executeUpdate();
            List<String> tagIdLst = insertTags(article, ps);

            for (String tagid : tagIdLst) {
                String sql = "insert into article_tags values(?,?)";
                ps = connection.prepareStatement(sql);
                ps.setString(1, article.getId());
                ps.setString(2, tagid);
                ps.executeUpdate();
            }
            System.out.println("文章发表成功");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }

    private static List<String> insertTags(Article article, PreparedStatement ps) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String[] arrTags = article.getTags().split(",");
        List<String> tagIdLst = new ArrayList<>();
        for (String tag : arrTags) {
            String sqlTag = "select id from tags where name = ?";
            ps = connection.prepareStatement(sqlTag);
            ps.setString(1, tag);
            ResultSet resultSet = ps.executeQuery();
            String tag_id;
            if (resultSet.next()) {
                tag_id = resultSet.getString("id");
            } else {
                String sqlInsertTag = "insert into tags values(?,?)";
                ps = connection.prepareStatement(sqlInsertTag);
                tag_id = UUID.randomUUID().toString();
                ps.setString(1, tag_id);
                ps.setString(2, tag);
                ps.executeUpdate();
            }
            tagIdLst.add(tag_id);
        }
        return tagIdLst;
    }

    public void takeFavorite(String article_id, String user_id) {
        Connection connection = JDBCUtils.getConnection();
        String sqlFavorite = "select count(*) as cnt from article_favorites where user_id = ? AND article_id=?";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sqlFavorite);
            ps.setString(1, user_id);
            ps.setString(2, article_id);
            ResultSet resultSet = ps.executeQuery();
            if (resultSet.next()) {
                int count = resultSet.getInt("cnt");
                if (count != 0) {
                    String sqlDeleteTag = "Delete from article_favorites where user_id = ? AND article_id=?";
                    ps = connection.prepareStatement(sqlDeleteTag);
                    ps.setString(1, user_id);
                    ps.setString(2, article_id);
                } else {
                    String sqlInsertTag = "insert into article_favorites values(?,?)";
                    ps = connection.prepareStatement(sqlInsertTag);
                    ps.setString(1, article_id);
                    ps.setString(2, user_id);
                    ps.executeUpdate();
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public void takeCommand(String content, String article_id, String user_id, String created_at, String updated_at){
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement ps = null;
        String sqlInsert = "insert into comments values(?,?,?,?,?,?)";
        UUID uuid = UUID.randomUUID();
        try {
            ps = connection.prepareStatement(sqlInsert);
            ps.setString(1, uuid.toString());
            ps.setString(2, content);
            ps.setString(3, article_id);
            ps.setString(4, user_id);
            ps.setString(5, created_at);
            ps.setString(6, updated_at);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }


    }
}
